/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package SQLAPI;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author anvil
 */
public class SQL {
    
    private final SQLBroker sb;

    public SQL() {
        this.sb = new SQLBroker();
    }
    
    private boolean checkLoginInfo(String username, String password)
    {
        try {
            String unHolder,passHolder;
            sb.connect();
            ResultSet rs=sb.getAllUsers();
            while(rs.next())
            {
                unHolder=rs.getString(1);
                passHolder=rs.getString(2);
                if(unHolder.equals(username) && passHolder.equals(password))
                {
                    sb.close();
                    return true;
                }
            }
         sb.close();   
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public String login(String username, String password)
    {
        boolean flag=checkLoginInfo(username,password);
        String firstname=null;
        
        if(flag=true)
        {
            sb.connect();
            firstname=sb.getFirstName(username);
            sb.toggleFlag(username);
            int empid=sb.getEmpId(username);
            sb.insertUsageData(empid, "Logged in");
            sb.close();
            return firstname;
        }
        if(flag==false)
        {
            System.out.println("Login has Failed.");
            return firstname;
        }
        return firstname;
    }
    
    public void logout(String username)
    {
        sb.connect();
        sb.toggleFlag(username);
        int empid=sb.getEmpId(username);
        sb.insertUsageData(empid,"Logged out");
        sb.close();
    }
    
    public ArrayList getWebUsers()
    {
        ArrayList<Employee> list = new ArrayList();
        int empid;
        String fname,lname,email,wphone,mphone;
        
        try{
            sb.connect();
            ResultSet rs=sb.getAllWebUsers();
            while(rs.next())
            {
                empid=rs.getInt(1);
                fname=rs.getString(2);
                lname=rs.getString(3);
                email=rs.getString(9);
                wphone=rs.getString(10);
                mphone=rs.getString(11);
                Employee e = new Employee(empid, fname, lname, email, wphone, mphone);
                list.add(e);
            }
            sb.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    
    public ArrayList getUsageReports()
    {
        ArrayList<UsageReport> list = new ArrayList();
        String userid,reportid,time,message;
        try {
            sb.connect();
            ResultSet rs=sb.getUsageData();
            while(rs.next())
            {
                userid=rs.getString(1);
                reportid=rs.getString(2);
                time=rs.getString(3);
                message=rs.getString(4);
                UsageReport rpt = new UsageReport(userid,reportid,time,message);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    
    public ArrayList getTransactionReports()
    {
        ArrayList<TransactionReport> list = new ArrayList();
        try {
            String reportid,transid,time,flag,desc;
            sb.connect();
            ResultSet rs = sb.getTransactionData();
            while(rs.next())
            {
                reportid=rs.getString(1);
                transid=rs.getString(2);
                time=rs.getString(3);
                flag=rs.getString(4);
                desc=rs.getString(5);
                TransactionReport rpt = new TransactionReport(reportid, transid, time, flag, desc);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
            return list;
    }
    
    public static void generateTransactionReport(String employeeID, String message)
    {
        
    }
    
    public void generateUsageReport(int empid, String message)
    {
        sb.connect();
        sb.insertUsageData(empid, message);
        sb.close();
    }
}
